package com.dbclass;

import java.util.ArrayList;

import com.objects.Latlng;
import com.objects.ObjRes;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import static android.provider.BaseColumns._ID;

public class DBHelperTaxi extends SQLiteOpenHelper {
	private static String DBNAME = "databaseTaximetro.db";
	private static int DATABASEVERSION = 1;

	private static final String TABLENAME3 = "res";
	private static final String TABLENAME4 = "latlng";
	private static final String TABLENAME5 = "taxi_data";
	private static final String TABLENAME6 = "ini";
	private static final String TABLENAME7 = "ter";

	private static final String SQLCREATE3 = "CREATE TABLE res ("
			+ _ID
			+ " INTEGER PRIMARY KEY,"
			+ " ResPapelet INTEGER, resDirLat TEXT, resDirLon TEXT, res2DirLat TEXT, resZarRec TEXT, res2DirLon TEXT, resMovil TEXT, rescomcod TEXT,"
			+ " rescalnom TEXT, resclinum TEXT, resMCto TEXT, resMCtoFH TEXT,  resFecPre TEXT, resCliNom TEXT,"
			+ " res2CalNom TEXT, res2CliNum TEXT, res2ComCod TEXT, resHrsPre TEXT, resHrsRet TEXT,"
			+ " resFecRet TEXT, resMPos TEXT, resMPosFH TEXT,  nombreConductor TEXT, nombreComunaOrigen TEXT, nombreComunaDestino TEXT,"
			+ " kilometros TEXT, tiempoSeg TEXT, tiempoTotal TEXT, esperadoMonto TEXT, tarifa TEXT, "
			+ " jc_disp TEXT, jc_valor TEXT, jc_check INTEGER, "
			+ " aloj_disp TEXT, aloj_valor TEXT, aloj_check INTEGER, col_disp TEXT, col_valor TEXT, col_check INTEGER, "
			+ " mj_disp TEXT, mj_valor TEXT, mj_check INTEGER, s5s_disp TEXT, s5s_valor TEXT, s5s_check INTEGER, "
			+ " s6s_disp TEXT, s6s_valor TEXT, s6s_check INTEGER, s7s_disp TEXT, s7s_valor TEXT, s7s_check INTEGER, "
			+ " he_valor TEXT, bb_disp TEXT, bb_valor TEXT, minima_valor TEXT, min_minimo INTEGER)";

	private static final String SQLCREATEPUNTOSGEOGRAFICOS = "CREATE TABLE latlng ("
			+ _ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ " folio TEXT, lat TEXT, lng TEXT, hora TEXT)";

	private static final String SQLCREATEDATOSTAXIMETRO = "CREATE TABLE taxi_data ("
			+ _ID
			+ " INTEGER PRIMARY KEY ,"
			+ " inicio_serv_milis TEXT,  termino_serv_milis TEXT, tiempo_esp TEXT, kilometros TEXT)";

	private static final String SQLCREATEINICIADO = "CREATE TABLE ini (" + _ID
			+ " INTEGER PRIMARY KEY ," + " estado TEXT)";

	private static final String SQLCREATETERMINADO = "CREATE TABLE ter (" + _ID
			+ " INTEGER PRIMARY KEY ," + " estado TEXT)";

	public DBHelperTaxi(Context context) {
		super(context, DBNAME, null, DATABASEVERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		db.execSQL(SQLCREATE3);
		db.execSQL(SQLCREATEPUNTOSGEOGRAFICOS);
		db.execSQL(SQLCREATEDATOSTAXIMETRO);
		db.execSQL(SQLCREATEINICIADO);
		db.execSQL(SQLCREATETERMINADO);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldversiom, int newversion) {

	}

	public void InsertarRes(ObjRes Reserva, int folio) {
		
		int resp = ObtenerResExist(folio);
			
			if(resp==0)
			{
				InsertarEstIni(0, folio);
				InsertarEstTer(0, folio);
			}
		
		
		SQLiteDatabase db = getWritableDatabase();
	
		
		if (db != null) {

			ContentValues value = new ContentValues();
			value.put(_ID, folio);
			value.put("ResPapelet", Reserva.getResPapelet());
			value.put("resDirLat", Reserva.getResDirLat());
			value.put("resDirLon", Reserva.getResDirLon());
			value.put("res2DirLat", Reserva.getRes2DirLat());
			value.put("res2DirLon", Reserva.getRes2DirLon());
			value.put("resMovil", Reserva.getResMovil());
			value.put("rescomcod", Reserva.getRescomcod());
			value.put("rescalnom", Reserva.getRescalnom());
			value.put("resclinum", Reserva.getResclinum());
			value.put("resMCto", Reserva.getResMCto());
			value.put("resMCtoFH", Reserva.getResMCtoFH());
			value.put("resFecPre", Reserva.getResFecPre());
			value.put("resCliNom", Reserva.getResCliNom());
			value.put("res2CalNom", Reserva.getRes2CalNom());
			value.put("res2CliNum", Reserva.getRes2CliNum());
			value.put("res2ComCod", Reserva.getRes2ComCod());
			value.put("resHrsPre", Reserva.getResHrsPre());
			value.put("resZarRec", Reserva.getResZarRec());
			value.put("resHrsRet", Reserva.getResHrsRet()); 
			value.put("resFecRet", Reserva.getResFecRet());
			value.put("resMPos", Reserva.getResMPos());
			value.put("resMPosFH", Reserva.getResMPosFH());
			value.put("nombreConductor", Reserva.getNombreConductor());
			value.put("nombreComunaOrigen", Reserva.getNombreComunaOrigen());
			value.put("nombreComunaDestino", Reserva.getNombreComunaDestino());
			value.put("kilometros", Reserva.getKilometros());
			value.put("tiempoSeg", Reserva.getTiempoSeg());
			value.put("tiempoTotal", Reserva.getTiempoTotal());
			value.put("esperadoMonto", Reserva.getEsperadoMonto());
			value.put("tarifa", Reserva.getTarifa());

			value.put("jc_disp", Reserva.getJc_disp());
			value.put("jc_valor", Reserva.getJc_valor());
			value.put("jc_check", Reserva.getJc_check());
			value.put("aloj_disp", Reserva.getAloj_disp());
			value.put("aloj_valor", Reserva.getAloj_valor());
			value.put("aloj_check", Reserva.getAloj_check());
			value.put("col_disp", Reserva.getCol_disp());
			value.put("col_valor", Reserva.getCol_valor());
			value.put("col_check", Reserva.getCol_check());
			value.put("mj_disp", Reserva.getMj_disp());
			value.put("mj_valor", Reserva.getMj_valor());

			value.put("mj_check", Reserva.getMj_check());
			value.put("s5s_disp", Reserva.getS5s_disp());
			value.put("s5s_valor", Reserva.getS5s_valor());
			value.put("s5s_check", Reserva.getS5s_check());
			value.put("s6s_disp", Reserva.getS6s_disp());
			value.put("s6s_valor", Reserva.getS6s_valor());
			value.put("s6s_check", Reserva.getS6s_check());
			value.put("s7s_disp", Reserva.getS7s_disp());
			value.put("s7s_valor", Reserva.getS7s_valor());
			value.put("s7s_check", Reserva.getS7s_check());
			value.put("he_valor", Reserva.getHe_valor());
			value.put("bb_disp", Reserva.getBb_check());
			value.put("bb_valor", Reserva.getBb_valor());
			value.put("minima_valor", Reserva.getMinima_valor());
			value.put("min_minimo", Reserva.getMin_minimo());


			db.insertWithOnConflict(TABLENAME3, null, value,
					SQLiteDatabase.CONFLICT_REPLACE);

		}
		cerrardb();
	}

	public ObjRes ObtenerRes(int folio) {
		SQLiteDatabase db = getReadableDatabase();

		ObjRes retorno = null;
		
	
		
		if (db != null) {

			String[] colum = new String[] { _ID, "ResPapelet" , "resDirLat", "resDirLon",
					"res2DirLat", "res2DirLon", "resMovil", "rescomcod",
					"rescalnom", "resclinum", "resMCto", "resMCtoFH","resFecPre",
					"resCliNom", "res2CalNom", "res2CliNum", "res2ComCod",
					"resHrsPre", "resHrsRet", "resFecRet", "resMPos", "resMPosFH",
					"nombreConductor", "nombreComunaOrigen", "resZarRec",
					"nombreComunaDestino", "kilometros", "tiempoSeg",
					"tiempoTotal", "esperadoMonto", "tarifa", "jc_disp",
					"jc_valor", "jc_check", "aloj_disp", "aloj_valor",
					"aloj_check", "col_disp", "col_valor", "col_check",
					"mj_disp", "mj_valor", "mj_check", "s5s_disp", "s5s_valor",
					"s5s_check", "s6s_disp", "s6s_valor", "s6s_check",
					"s7s_disp", "s7s_valor", "s7s_check", "he_valor", "bb_disp", "bb_valor", "minima_valor", "min_minimo" };
			Cursor cur = db.query(TABLENAME3, colum, _ID + "=" + folio, null,
					null, null, null);
			if (cur.moveToFirst()) {
				do {

					Log.i("he_valor", cur.getString(cur
							.getColumnIndex("he_valor")));

					retorno = new ObjRes(
							cur.getInt(cur.getColumnIndex(_ID)),
							cur.getInt(cur.getColumnIndex("ResPapelet")),
							cur.getString(cur.getColumnIndex("resZarRec")),
							cur.getString(cur.getColumnIndex("resDirLat")),
							cur.getString(cur.getColumnIndex("resDirLon")),
							cur.getString(cur.getColumnIndex("res2DirLat")),
							cur.getString(cur.getColumnIndex("res2DirLon")),
							cur.getString(cur.getColumnIndex("resMovil")),
							cur.getString(cur.getColumnIndex("rescomcod")),
							cur.getString(cur.getColumnIndex("rescalnom")),
							cur.getString(cur.getColumnIndex("resclinum")),
							cur.getString(cur.getColumnIndex("resMCto")),
							cur.getString(cur.getColumnIndex("resMCtoFH")),
							cur.getString(cur.getColumnIndex("resFecPre")),
							cur.getString(cur.getColumnIndex("resCliNom")),
							cur.getString(cur.getColumnIndex("res2CalNom")),
							cur.getString(cur.getColumnIndex("res2CliNum")),
							cur.getString(cur.getColumnIndex("res2ComCod")),
							cur.getString(cur.getColumnIndex("resHrsPre")),
							cur.getString(cur.getColumnIndex("resHrsRet")),
							cur.getString(cur.getColumnIndex("resFecRet")),
							cur.getString(cur.getColumnIndex("resMPos")),
							cur.getString(cur.getColumnIndex("resMPosFH")),
							cur.getString(cur.getColumnIndex("nombreConductor")),
							cur.getString(cur
									.getColumnIndex("nombreComunaOrigen")),
							cur.getString(cur
									.getColumnIndex("nombreComunaDestino")),
							cur.getString(cur.getColumnIndex("kilometros")),
							cur.getString(cur.getColumnIndex("tiempoSeg")), cur
									.getString(cur
											.getColumnIndex("tiempoTotal")),
							cur.getString(cur.getColumnIndex("esperadoMonto")),
							cur.getString(cur.getColumnIndex("tarifa")), cur
									.getString(cur.getColumnIndex("jc_disp")),
							cur.getString(cur.getColumnIndex("jc_valor")), cur
									.getInt(cur.getColumnIndex("jc_check")),
							cur.getString(cur.getColumnIndex("aloj_disp")),
							cur.getString(cur.getColumnIndex("aloj_valor")),
							cur.getInt(cur.getColumnIndex("aloj_check")), cur
									.getString(cur.getColumnIndex("col_disp")),
							cur.getString(cur.getColumnIndex("col_valor")), cur
									.getInt(cur.getColumnIndex("col_check")),
							cur.getString(cur.getColumnIndex("mj_disp")), cur
									.getString(cur.getColumnIndex("mj_valor")),
							cur.getInt(cur.getColumnIndex("mj_check")), cur
									.getString(cur.getColumnIndex("s5s_disp")),
							cur.getString(cur.getColumnIndex("s5s_valor")), cur
									.getInt(cur.getColumnIndex("s5s_check")),
							cur.getString(cur.getColumnIndex("s6s_disp")),
							cur.getString(cur.getColumnIndex("s6s_valor")), cur
									.getInt(cur.getColumnIndex("s6s_check")),
							cur.getString(cur.getColumnIndex("s7s_disp")),
							cur.getString(cur.getColumnIndex("s7s_valor")), cur
									.getInt(cur.getColumnIndex("s7s_check")),
							cur.getString(cur.getColumnIndex("he_valor")),
							cur.getString(cur.getColumnIndex("bb_disp")),
							cur.getString(cur.getColumnIndex("bb_valor")),
							cur.getString(cur.getColumnIndex("minima_valor")), 
							cur.getInt(cur.getColumnIndex("min_minimo")));
				} while (cur.moveToNext());

			}
			cur.close();
		}

		return retorno;

	}
	
	public int ObtenerResExist(int folio) {
		SQLiteDatabase db = getReadableDatabase();

		int retorno = 0;
		if (db != null) {

			String[] colum = new String[] { _ID };
			Cursor cur = db.query(TABLENAME3, colum, _ID + "=" + folio, null,
					null, null, null);
			if (cur.moveToFirst()) {
				do {
					retorno = 1;
				} while (cur.moveToNext());

			}
			cur.close();
		}

		return retorno;

	}

	public void BorrarRes() {
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL("DROP TABLE IF EXISTS " + TABLENAME3);
		db.execSQL(SQLCREATE3);

	}

	public void Borrarlat() {
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL("DROP TABLE IF EXISTS " + TABLENAME4);
		db.execSQL(SQLCREATEPUNTOSGEOGRAFICOS);

	}

	public void cerrardb() {
		SQLiteDatabase db = getWritableDatabase();
		db.close();
	}

	public ObjRes VerificarResExistente() {

		ObjRes retorno = null;

		SQLiteDatabase db = getWritableDatabase();

		return null;
	}

	public void InsertarLatLng(double lat, double lng, String hora, int folio) {
		SQLiteDatabase db = getWritableDatabase();
		if (db != null) {

			ContentValues value = new ContentValues();

			value.put("folio", folio);
			value.put("lat", lat);
			value.put("lng", lng);
			value.put("hora", hora);

			db.insert(TABLENAME4, null, value);

		}
		cerrardb();
	}


	public void InsertarEstIni(int est, int folio) {
		SQLiteDatabase db = getWritableDatabase();

		db.execSQL("DROP TABLE IF EXISTS " + TABLENAME6);
		db.execSQL(SQLCREATEINICIADO);

		if (db != null) {

			ContentValues value = new ContentValues();

			value.put(_ID, folio);
			value.put("estado", est);

			db.insert(TABLENAME6, null, value);

		}
		cerrardb();
	}

	public void InsertarEstTer(int est, int folio) {
		SQLiteDatabase db = getWritableDatabase();

		db.execSQL("DROP TABLE IF EXISTS " + TABLENAME7);
		db.execSQL(SQLCREATETERMINADO);

		if (db != null) {

			ContentValues value = new ContentValues();

			value.put(_ID, folio);
			value.put("estado", est);

			db.insert(TABLENAME7, null, value);

		}
		cerrardb();
	}

	public ArrayList<Latlng> ObtenerLatLng(int folio) {
		SQLiteDatabase db = getReadableDatabase();

		ArrayList<Latlng> retorno = new ArrayList<Latlng>();
		if (db != null) {

			String[] colum = new String[] { "folio", "lat", "lng", "hora" };
			Cursor cur = db.query(TABLENAME4, colum, "folio" + "=" + folio,
					null, null, null, null);
			if (cur.moveToFirst()) {
				do {


					retorno.add(new Latlng(cur.getInt(cur
							.getColumnIndex("folio")), cur.getDouble(cur
							.getColumnIndex("lat")), cur.getDouble(cur
							.getColumnIndex("lng")), cur.getString(cur
							.getColumnIndex("hora"))));

				} while (cur.moveToNext());

			}
			cur.close();
		}

		return retorno;

	}

	public int ObtenerEstIni(int folio) {
		SQLiteDatabase db = getReadableDatabase();

		int retorno = 2;
		if (db != null) {

			String[] colum = new String[] { "estado" };
			Cursor cur = db.query(TABLENAME6, colum, _ID + "=" + folio, null,
					null, null, null);
			if (cur.moveToFirst()) {
				do {

					retorno = cur.getInt(cur.getColumnIndex("estado"));

				} while (cur.moveToNext());

			}
			cur.close();
		}

		return retorno;

	}

	public int ObtenerEstTer(int folio) {
		SQLiteDatabase db = getReadableDatabase();

		int retorno = 2;
		if (db != null) {

			String[] colum = new String[] { "estado" };
			Cursor cur = db.query(TABLENAME7, colum, _ID + "=" + folio, null,
					null, null, null);
			if (cur.moveToFirst()) {
				do {

					retorno = cur.getInt(cur.getColumnIndex("estado"));

				} while (cur.moveToNext());

			}
			cur.close();
		}

		return retorno;

	}
	
	public String ObtenerTespera(int folio) {
		SQLiteDatabase db = getReadableDatabase();

		String retorno = " ";
		if (db != null) {

			String[] colum = new String[] { "tiempo_esp" };
			Cursor cur = db.query(TABLENAME5, colum, _ID + "=" + folio, null,
					null, null, null);
			if (cur.moveToFirst()) {
				do {

					retorno = cur.getString(cur.getColumnIndex("tiempo_esp"));

				} while (cur.moveToNext());

			}
			cur.close();
		}

		return retorno;

	}
	
	public String ObtenerTIniMili(int folio) {
		SQLiteDatabase db = getReadableDatabase();

		String retorno = " ";
		if (db != null) {

			String[] colum = new String[] { "inicio_serv_milis" };
			Cursor cur = db.query(TABLENAME5, colum, _ID + "=" + folio, null,
					null, null, null);
			if (cur.moveToFirst()) {
				do {

					retorno = cur.getString(cur.getColumnIndex("inicio_serv_milis"));

				} while (cur.moveToNext());

			}
			cur.close();
		}

		return retorno;

	}
	
	public String ObtenerTTerMili(int folio) {
		SQLiteDatabase db = getReadableDatabase();

		String retorno = " ";
		if (db != null) {

			String[] colum = new String[] { "termino_serv_milis" };
			Cursor cur = db.query(TABLENAME5, colum, _ID + "=" + folio, null,
					null, null, null);
			if (cur.moveToFirst()) {
				do {

					retorno = cur.getString(cur.getColumnIndex("termino_serv_milis"));

				} while (cur.moveToNext());

			}
			cur.close();
		}

		return retorno;

	}
	
	public double ObtenerKilometros(int folio) {
		SQLiteDatabase db = getReadableDatabase();

		double retorno = 0;
		if (db != null) {

			String[] colum = new String[] { "kilometros" };
			Cursor cur = db.query(TABLENAME5, colum, _ID + "=" + folio, null,
					null, null, null);
			if (cur.moveToFirst()) {
				do {

					retorno = cur.getDouble(cur.getColumnIndex("kilometros"));

				} while (cur.moveToNext());

			}
			cur.close();
		}

		return retorno;

	}
	
	public void CrearTaxiData(int folio) {
		SQLiteDatabase db = getWritableDatabase();
		if (db != null) {

			ContentValues value = new ContentValues();

			value.put(_ID, folio);
			value.put("tiempo_esp", "0");
			
			value.put("inicio_serv_milis", "0");
			
			value.put("termino_serv_milis", "0");
			
			value.put("kilometros", "0");
			
			db.insert(TABLENAME5, null, value);

		}
		cerrardb();

	}
	
	public void UpdateTaxiDataTespera(int folio, String milis) {
		SQLiteDatabase db = getWritableDatabase();
		if (db != null) {

			ContentValues value = new ContentValues();

			value.put(_ID, folio);
			value.put("tiempo_esp", milis);
			
			
			db.update(TABLENAME5, value, _ID + "=" + folio, null);
		}
		cerrardb();

	}
	
	public void UpdateTaxiDataKilometros(int folio, String kilo) {
		SQLiteDatabase db = getWritableDatabase();
		if (db != null) {

			ContentValues value = new ContentValues();

			value.put(_ID, folio);
			value.put("kilometros", kilo);
			
			
			db.update(TABLENAME5, value, _ID + "=" + folio, null);
		}
		cerrardb();

	}
	
	public void UpdateTaxiDataTIni(int folio, long milis) {
		SQLiteDatabase db = getWritableDatabase();
		if (db != null) {

			ContentValues value = new ContentValues();

			value.put(_ID, folio);
			
			value.put("inicio_serv_milis", milis);
			
			db.update(TABLENAME5, value, _ID + "=" + folio, null);
			
			

		}
		cerrardb();

	}
	
	public void UpdateTaxiDataTTer(int folio, long milis) {
		SQLiteDatabase db = getWritableDatabase();
		if (db != null) {

			ContentValues value = new ContentValues();

			value.put(_ID, folio);
			
			value.put("termino_serv_milis", milis);
			
			db.update(TABLENAME5, value, _ID + "=" + folio, null);

		}
		cerrardb();

	}
	

	public int ExisteTaxiData(int folio) {
		SQLiteDatabase db = getReadableDatabase();

		int retorno =0;
		if (db != null) {

			String[] colum = new String[] { _ID };
			Cursor cur = db.query(TABLENAME5, colum, _ID + "=" + folio, null,
					null, null, null);
			if (cur.moveToFirst()) {
				do {

					retorno = 1;

				} while (cur.moveToNext());

			}
			cur.close();
		}

		return retorno;

	}
	
}
